﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Runtime.Remoting.Metadata.W3cXsd2001;
using System.Security.Policy;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;
using CoreShop.Library;
using CoreShop.Model;
using CoreShop.Model.UI;
using CoreShop.Repository;
using Newtonsoft.Json;
using SqlSugar;


namespace CoreShop.Services
{
    /// <summary>
    /// 接口实现
    /// </summary>
    public class activitymasterServices : Repository<activitymaster>, IactivitymasterServices
    {
        #region 实现重写增删改查操作

        /// <summary>
        /// 重写异步插入方法
        /// </summary>
        /// <param name="entity">实体数据</param>
        /// <returns></returns>
        public bool InsertAsync(activitymaster entity)
        {
            return base.Change<activitymaster>().Insert(entity);
        }
        public bool InsertOrUpdate(List<activitymaster> entity)
        {
            return base.InsertOrUpdate(entity);
        }
        /// <summary>
        /// 重写异步更新方法方法
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool UpdateAsync(activitymaster entity)
        {
            return base.Change<activitymaster>().Update(a => new activitymaster()
            {
                activityInfoName = entity.activityInfoName,
            }, a => a.activityInfoId == entity.activityInfoId);
        }

        /// <summary>
        /// 重写异步更新方法方法
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool UpdateAsync(List<activitymaster> entity)
        {
            bool returnKey = true;
            foreach (var item in entity)
            {
                returnKey = base.Change<activitymaster>().Update(a => new activitymaster()
                {
                    activityInfoName = item.activityInfoName,
                }, a => a.activityInfoId == item.activityInfoId);
            }
            return returnKey;
        }
               
        #endregion

        #region 获取缓存的所有数据

        /// <summary>
        /// 获取缓存的所有数据
        /// </summary>
        /// <returns></returns>
        public List<activitymaster> GetCaChe()
        {
            return base.GetList();
        }

        #endregion

        #region 获取购物车的所有促销数据

        /// <summary>
        /// 获取购物车的所有促销数据
        /// </summary>
        /// <returns></returns>
        public List<ActivityOrderingDto> GetSpendingEnoughList(corecmsfoodorderingmaster master
            , List<corecmsfoodordering> orderings, corecmsuser member, List<OrderingGoodsTypeDto> orderingTypes)
        {
            decimal dRealAmount = orderings.Sum(p => p.realAmount);
            List<long> orderingTypeIds = orderingTypes.Select(p=>p.foodTypeId).Distinct().ToList();
            string typeStrIds = string.Join(",", orderingTypeIds);//分类IDS

            StringBuilder strSql = new StringBuilder();
            #region SQL语句        
            //1、生成购物车临时表:temp_ordering
            strSql.Append($"CREATE TEMPORARY TABLE temp_ordering AS ");
            strSql.Append($"SELECT foodSpecId,Sum(realAmount) as realAmount,Sum(quantity) as quantity ");
            strSql.Append($"FROM corecmsfoodordering where orderId = {master.orderId} and dataType = 1 ");
            strSql.Append($"Group by foodSpecId;");
            //2、==>>>>符合条件的所有活动临时表:temp_master
            strSql.Append($"CREATE TEMPORARY TABLE temp_master AS ");
            strSql.Append($"SELECT a.activityInfoId ");
            strSql.Append($"FROM activitymaster as a INNER JOIN activitylineresp as b ");
            strSql.Append($"ON a.activityInfoId = b.activityInfoId ");
            strSql.Append($"where a.activityBeginDate <= now() and a.activityEndDate >= NOW() ");            
            strSql.Append($"and a.status = 1 and (a.channelId = 0 or a.channelId = 1) ");
            if (member != null )
                strSql.Append($"and (a.onlyVip = {(int)GlobalEnumVars.ActivityOnlyVip.All} or a.onlyVip = {(int)GlobalEnumVars.ActivityOnlyVip.Member}) ");
            else
                strSql.Append($"and (a.onlyVip = {(int)GlobalEnumVars.ActivityOnlyVip.All} or a.onlyVip = {(int)GlobalEnumVars.ActivityOnlyVip.Ordinary}) ");
            strSql.Append($"and (b.condition1 <= {dRealAmount} and a.activitySubTypeId = {(int)GlobalEnumVars.ActivitySubType.满减活动}) "); 
            strSql.Append($"Group by a.activityInfoId;");

            //3.1、用temp_ordering和temp_master生成明细表：temp_detail,goodsType=2商品
            strSql.Append($"CREATE TEMPORARY TABLE temp_detail AS SELECT a.activityInfoId,");
            strSql.Append($"c.pdtSpuSpecId as itemId,a.goodsType,d.realAmount,d.quantity,a.activitySubTypeId ");
            strSql.Append($"FROM activitymaster as a INNER JOIN temp_master as b ");
            strSql.Append($"ON a.activityInfoId = b.activityInfoId INNER JOIN activitygoods as c ");
            strSql.Append($"ON a.activityInfoId = c.activityInfoId INNER JOIN temp_ordering as d ");
            strSql.Append($"ON c.pdtSpuSpecId = d.foodSpecId where a.goodsType = {(int)GlobalEnumVars.ActivityGoodsType.Goods};");

            //3.2、添加商品分类到temp_detail,goodsType=1分类
            strSql.Append($"INSERT INTO temp_detail(activityInfoId, itemId,goodsType,realAmount,quantity,activitySubTypeId) ");
            strSql.Append($"SELECT a.activityInfoId, c.itemSortId as itemId,a.goodsType,0 as realAmount,0 as quantity, a.activitySubTypeId ");
            strSql.Append($"FROM activitymaster as a INNER JOIN temp_master as b ");
            strSql.Append($"ON a.activityInfoId = b.activityInfoId INNER JOIN activitysort as c ");
            strSql.Append($"ON a.activityInfoId = c.activityInfoId ");
            strSql.Append($"where c.itemSortId in ({typeStrIds}) and c.excludeFLag = 0 ");
            strSql.Append($"and a.goodsType = {(int)GlobalEnumVars.ActivityGoodsType.Type};");

            //3.3、添加全场的类型到temp_detail,goodsType=0全场
            strSql.Append($"INSERT INTO temp_detail(activityInfoId, itemId,goodsType,realAmount,quantity,activitySubTypeId) ");
            strSql.Append($"SELECT a.activityInfoId, 0,a.goodsType,{dRealAmount},0,a.activitySubTypeId ");
            strSql.Append($"FROM activitymaster as a INNER JOIN temp_master as b ");
            strSql.Append($"ON a.activityInfoId = b.activityInfoId where a.goodsType = {(int)GlobalEnumVars.ActivityGoodsType.All};");
                       
            strSql.Append($"select * from temp_detail order by activityInfoId desc;");            
            strSql.Append($"DROP TEMPORARY TABLE IF EXISTS temp_detail;");
            strSql.Append($"DROP TEMPORARY TABLE IF EXISTS temp_master;");
            strSql.Append($"DROP TEMPORARY TABLE IF EXISTS temp_ordering;");
            #endregion
            var SqlClient = base.GetInstance();
            var list = SqlClient.Ado.SqlQuery<ActivityOrderingDto>(strSql.ToString());
            return list;
        }

        /// <summary>
        /// 获取购物车的所有促销数据
        /// </summary>
        /// <returns></returns>
        public List<ActivityOrderingDto> GetFoldingActivityList(corecmsfoodorderingmaster master
            , List<corecmsfoodordering> orderings, corecmsuser member, List<OrderingGoodsTypeDto> orderingTypes)
        {
            decimal dAmount = orderings.Sum(p => p.realAmount);
            decimal dQuantity = orderings.Sum(p => p.quantity);
            List<long> orderingTypeIds = orderingTypes.Select(p => p.foodTypeId).Distinct().ToList();
            string typeStrIds = string.Join(",", orderingTypeIds);//分类IDS

            StringBuilder strSql = new StringBuilder();
            #region SQL语句        
            //1、生成购物车临时表:temp_ordering
            strSql.Append($"CREATE TEMPORARY TABLE temp_ordering AS ");
            strSql.Append($"SELECT foodSpecId,Sum(realAmount) as realAmount,Sum(quantity) as quantity ");
            strSql.Append($"FROM corecmsfoodordering where orderId = {master.orderId} and dataType = 1 ");
            strSql.Append($"Group by foodSpecId;");
            //2、==>>>>符合条件的所有活动临时表:temp_master
            strSql.Append($"CREATE TEMPORARY TABLE temp_master AS ");
            strSql.Append($"SELECT a.activityInfoId ");
            strSql.Append($"FROM activitymaster as a INNER JOIN activitylineresp as b ");
            strSql.Append($"ON a.activityInfoId = b.activityInfoId ");
            strSql.Append($"where a.activityBeginDate <= now() and a.activityEndDate >= NOW() ");
            strSql.Append($"and a.status = 1 and (a.channelId = 0 or a.channelId = 1) ");
            if (member != null)
                strSql.Append($"and (a.onlyVip = {(int)GlobalEnumVars.ActivityOnlyVip.All} or a.onlyVip = {(int)GlobalEnumVars.ActivityOnlyVip.Member}) ");
            else
                strSql.Append($"and (a.onlyVip = {(int)GlobalEnumVars.ActivityOnlyVip.All} or a.onlyVip = {(int)GlobalEnumVars.ActivityOnlyVip.Ordinary}) ");            
            strSql.Append($"and (b.condition1 <= {dQuantity} and a.activitySubTypeId = {(int)GlobalEnumVars.ActivitySubType.满折活动}) ");
            strSql.Append($"Group by a.activityInfoId;");

            //3.1、用temp_ordering和temp_master生成明细表：temp_detail,goodsType=2商品
            strSql.Append($"CREATE TEMPORARY TABLE temp_detail AS SELECT a.activityInfoId,");
            strSql.Append($"c.pdtSpuSpecId as itemId,a.goodsType,d.realAmount,d.quantity,a.activitySubTypeId ");
            strSql.Append($"FROM activitymaster as a INNER JOIN temp_master as b ");
            strSql.Append($"ON a.activityInfoId = b.activityInfoId INNER JOIN activitygoods as c ");
            strSql.Append($"ON a.activityInfoId = c.activityInfoId INNER JOIN temp_ordering as d ");
            strSql.Append($"ON c.pdtSpuSpecId = d.foodSpecId where a.goodsType = {(int)GlobalEnumVars.ActivityGoodsType.Goods};");

            //3.2、添加商品分类到temp_detail,goodsType=1分类
            strSql.Append($"INSERT INTO temp_detail(activityInfoId, itemId,goodsType,realAmount,quantity,activitySubTypeId) ");
            strSql.Append($"SELECT a.activityInfoId, c.itemSortId as itemId,a.goodsType,0 as realAmount,0 as quantity, a.activitySubTypeId ");
            strSql.Append($"FROM activitymaster as a INNER JOIN temp_master as b ");
            strSql.Append($"ON a.activityInfoId = b.activityInfoId INNER JOIN activitysort as c ");
            strSql.Append($"ON a.activityInfoId = c.activityInfoId ");
            strSql.Append($"where c.itemSortId in ({typeStrIds}) and c.excludeFLag = 0 ");
            strSql.Append($"and a.goodsType = {(int)GlobalEnumVars.ActivityGoodsType.Type};");

            //3.3、添加全场的类型到temp_detail,goodsType=0全场
            strSql.Append($"INSERT INTO temp_detail(activityInfoId, itemId,goodsType,realAmount,quantity,activitySubTypeId) ");
            strSql.Append($"SELECT a.activityInfoId, 0,a.goodsType,{dAmount},{dQuantity},a.activitySubTypeId ");
            strSql.Append($"FROM activitymaster as a INNER JOIN temp_master as b ");
            strSql.Append($"ON a.activityInfoId = b.activityInfoId where a.goodsType = {(int)GlobalEnumVars.ActivityGoodsType.All};");

            strSql.Append($"select * from temp_detail order by activityInfoId desc;");
            strSql.Append($"DROP TEMPORARY TABLE IF EXISTS temp_detail;");
            strSql.Append($"DROP TEMPORARY TABLE IF EXISTS temp_master;");
            strSql.Append($"DROP TEMPORARY TABLE IF EXISTS temp_ordering;");
            #endregion
            var SqlClient = base.GetInstance();
            var list = SqlClient.Ado.SqlQuery<ActivityOrderingDto>(strSql.ToString());
            return list;
        }

        #endregion

    }
}